using System;
using System.Collections.Generic;
using System.Text;
using ScrewTurn.Wiki.PluginFramework;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace WikiSQLTableFormatter
{
    public abstract class SQLTableFormatterBase
    {
        protected string config;
        protected IHost host;
        //SqlFataException is the number to start reporting SQL errors to the Wiki log. (No Longer Needed)
        //const int SqlFatalException = 19;

        //Required function for ScrewTurn Wiki.
        public void Init(IHost host, string config)
        {
            this.host = host;
            this.config = config;
            if (!ValidateConfig())
                throw new InvalidConfigurationException();
        }

        //Required function for ScrewTurn Wiki.
        public void Shutdown() { }

        protected abstract bool ValidateConfig();

        /// <summary>
        /// Gets a new instance of SqlConnection from System.Data.SqlClient witht the configured SQL Server connection string.
        /// </summary>
        /// <returns>SqlConnection that was configured with the provider.</returns>
        protected SqlConnection GetConnection()
        {
            return new SqlConnection(config);
        }

        /// <summary>
        /// Gets a new instance of SqlCommand initilized with the configured System.Data.SqlClient.SqlConnection.
        /// </summary>
        /// <returns>SqlCommand with the configured SQL connection.</returns>
        protected SqlCommand GetCommand()
        {
            return GetConnection().CreateCommand();
        }

        /// <summary>
        /// Closes a System.Data.SqlClient.SqlConnection.
        /// </summary>
        /// <param name="command">SqlCommand command that will be closed.</param>
        /// <returns>Bool indicating success.</returns>
        protected bool Close(SqlCommand command)
        {
            try
            {
                command.Connection.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Executes a SQL query against a server.
        /// </summary>
        /// <param name="command">SqlCommand command.</param>
        /// <returns>SqlDataAdapter with the query results.</returns>
        protected DataSet ExecuteQuery(SqlCommand command)
        {
            DataSet results = new DataSet();
            try
            {
                SqlDataAdapter cmd = new SqlDataAdapter(command.CommandText, command.Connection);
                cmd.Fill(results);
            }
            catch (SqlException sex)
            {
                throw new Exception("There was an error in the SQL syntax or connection. " + sex);
            }
            catch (Exception ex)
            {
                host.LogEntry(ex.Message, LogEntryType.Error, this);
            }
            finally
            {
                Close(command);
            }
            return results;
        }

        /// <summary>
        /// Executes a SQL query against a server.
        /// </summary>
        /// <param name="command">Sqlcommand Command.</param>
        /// <param name="IsNonQuery">Indicates weather the command should be executed with no results.</param>
        /// <returns>Bool indicating success.</returns>
        protected bool ExecuteNonQuery(SqlCommand command)
        {
            try
            {
                command.Connection.Open();
                command.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sex)
            {
                throw new Exception("There was an error in the SQL syntax or connection: " + sex);
            }
            catch (Exception ex)
            {
                host.LogEntry(ex.Message, LogEntryType.Error, this);
                return false;
            }
            finally
            {
                Close(command);
            }
        }
    }
}
